﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CarLoanSystem.Entity;
using System.Data.SqlClient;
using System.Data;

namespace CarLoanSystem.DAL
{
    public class PaymentInfoDAL : DBBase
    {
        public static PaymentsItem LoadPayment(int id)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlDataReader dr;
                SqlCommand command = new SqlCommand(@"SELECT dbo.PAYMENTS.PAYMENTS_ID, dbo.PAYMENTS.LOAN_ID,
                                                dbo.PAYMENTS.EMPLOYEE_ID, dbo.PAYMENTS.PAYMENTS_DUE_DATE, 
                                                dbo.PAYMENTS.PAYMENTS_DATE, dbo.PAYMENTS.PAYMENTS_STATUS, dbo.LOAN.PAY_MONTH, 
                                                dbo.CUSTOMER.CUSTOMER_NAME + ' ' + dbo.CUSTOMER.CUSTOMER_LASTNAME AS NAME,
                                                dbo.CUSTOMER.CUSTOMER_PERSONAL_ID
                                                FROM dbo.LOAN INNER JOIN
                                                dbo.PAYMENTS ON dbo.LOAN.LOAN_ID = dbo.PAYMENTS.LOAN_ID INNER JOIN
                                                dbo.CUSTOMER ON dbo.LOAN.CUSTOMER_ID = dbo.CUSTOMER.CUSTOMER_ID
                                                WHERE [PAYMENTS_ID] = @PAYMENTS_ID" , DBBase.con);
                command.Parameters.AddWithValue("@PAYMENTS_ID", id);
                dr = command.ExecuteReader();
                DataTable dt = new DataTable();
                if (dr.HasRows)
                {
                    dt.Load(dr);
                }
                DBBase.DisConncetDatabase();
                if (dt.Rows.Count > 0)
                {
                    PaymentsItem item = new PaymentsItem();
                    item.EMPLOYEE_ID = Convert.ToInt32(dt.Rows[0]["EMPLOYEE_ID"].ToString());
                    item.LOAN_ID = Convert.ToInt32(dt.Rows[0]["LOAN_ID"].ToString());
                    if (!dt.Rows[0]["PAYMENTS_DATE"].ToString().Equals(""))
                    {
                        item.PAYMENTS_DATE = Convert.ToDateTime(dt.Rows[0]["PAYMENTS_DATE"].ToString());
                    }
                    item.PAYMENTS_DUE_DATE = Convert.ToDateTime(dt.Rows[0]["PAYMENTS_DUE_DATE"].ToString());
                    item.PAYMENTS_ID = Convert.ToInt32(dt.Rows[0]["PAYMENTS_ID"].ToString());
                    item.PAYMENTS_STATUS = dt.Rows[0]["PAYMENTS_STATUS"].ToString();
                    item.PAY_MONTH = Convert.ToDecimal(dt.Rows[0]["PAY_MONTH"].ToString());
                    item.CUSTOMER_NAME = dt.Rows[0]["NAME"].ToString();
                    item.CUSTOMER_PERSONAL_ID = dt.Rows[0]["CUSTOMER_PERSONAL_ID"].ToString();
                    return item;
                }
                else
                {
                    return null;
                }
            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public static DataTable LoadAllPayment(string id = "",string date = "")
        {
            try
            {
                string sqlQuery = @"SELECT dbo.PAYMENTS.PAYMENTS_ID, dbo.PAYMENTS.LOAN_ID,
                                    dbo.PAYMENTS.EMPLOYEE_ID, dbo.PAYMENTS.PAYMENTS_DUE_DATE, 
                                    dbo.PAYMENTS.PAYMENTS_DATE, dbo.PAYMENTS.PAYMENTS_STATUS,
                                    dbo.CUSTOMER.CUSTOMER_NAME + ' ' + dbo.CUSTOMER.CUSTOMER_LASTNAME As CUSTOMER_NAME,
                                    dbo.LOAN.PAY_MONTH
                                   FROM dbo.CUSTOMER INNER JOIN
                                    dbo.LOAN ON dbo.CUSTOMER.CUSTOMER_ID = dbo.LOAN.CUSTOMER_ID INNER JOIN
                                    dbo.PAYMENTS ON dbo.LOAN.LOAN_ID = dbo.PAYMENTS.LOAN_ID
                                    where dbo.PAYMENTS.PAYMENTS_STATUS = 'A'";

                if (id != "")
                {
                    sqlQuery += " and dbo.CUSTOMER.CUSTOMER_PERSONAL_ID = '" + id + "'";
                }
                if (date != "")
                {
                    sqlQuery += " and dbo.PAYMENTS.PAYMENTS_DUE_DATE <= '" + date + "'";
                }

                DBBase.ConncetDatabase();

                SqlCommand command = new SqlCommand(sqlQuery, DBBase.con);
                SqlDataReader dr = command.ExecuteReader();

                DataTable dt = new DataTable();
                if (dr.HasRows)
                {
                    dt.Load(dr);
                    DBBase.DisConncetDatabase();
                    return dt;
                }
                else
                {
                    DBBase.DisConncetDatabase();
                    return null;
                }

            }
            catch (Exception ex)
            {
                return null;
            }
        }

        public static void Insert(PaymentsItem item)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlCommand command = new SqlCommand(@"INSERT INTO [CAR_LOAN].[dbo].[PAYMENTS]
                                                       ([LOAN_ID]
                                                       ,[EMPLOYEE_ID]
                                                       ,[PAYMENTS_DUE_DATE]
                                                       ,[PAYMENTS_STATUS])
                                                 VALUES
                                                      ( @LOAN_ID
                                                       ,@EMPLOYEE_ID
                                                       ,@PAYMENTS_DUE_DATE
                                                       ,@PAYMENTS_STATUS)", DBBase.con);

                command.Parameters.AddWithValue("@LOAN_ID", item.LOAN_ID);
                command.Parameters.AddWithValue("@EMPLOYEE_ID", item.EMPLOYEE_ID);
                command.Parameters.AddWithValue("@PAYMENTS_DUE_DATE", item.PAYMENTS_DUE_DATE);
                command.Parameters.AddWithValue("@PAYMENTS_STATUS", item.PAYMENTS_STATUS);
                int tmp = command.ExecuteNonQuery();
                DBBase.DisConncetDatabase();
            }
            catch (Exception ex)
            {
                DBBase.DisConncetDatabase();
                throw ex;
            }
        }

        public static void Update(PaymentsItem item)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlCommand command = new SqlCommand(@"UPDATE [CAR_LOAN].[dbo].[PAYMENTS]
                                                    SET [PAYMENTS_DATE] = @PAYMENTS_DATE,
                                                        [PAYMENTS_STATUS] = @PAYMENTS_STATUS,
                                                        [EMPLOYEE_ID] = @EMPLOYEE_ID
                                                  WHERE ([PAYMENTS_ID] = @PAYMENTS_ID)"
                                                    , DBBase.con);

                command.Parameters.AddWithValue("@PAYMENTS_ID", item.PAYMENTS_ID);
                command.Parameters.AddWithValue("@PAYMENTS_DATE", item.PAYMENTS_DATE);
                command.Parameters.AddWithValue("@PAYMENTS_STATUS", item.PAYMENTS_STATUS);
                command.Parameters.AddWithValue("@EMPLOYEE_ID", item.EMPLOYEE_ID);
                int tmp = command.ExecuteNonQuery();
                DBBase.DisConncetDatabase();
            }
            catch (Exception ex)
            {
                DBBase.DisConncetDatabase();
                throw ex;
            }
        }

        public static bool CheckIsNotPayment(int id)
        {
            try
            {
                DBBase.ConncetDatabase();
                SqlDataReader dr;
                SqlCommand command = new SqlCommand(@"select LOAN_ID from PAYMENTS
                                                    where  LOAN_ID = @LOAN_ID and PAYMENTS_STATUS = 'A'", DBBase.con);
                command.Parameters.AddWithValue("@LOAN_ID", id);
                dr = command.ExecuteReader();
                DataTable dt = new DataTable();
                if (dr.HasRows)
                {
                    dt.Load(dr);
                }
                DBBase.DisConncetDatabase();
                if (dt.Rows.Count > 0)
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }
            catch (Exception ex)
            {
                return false;
            }
        }
    }
}
